<#assign objects = doc.objects>
package ${objects.package}.service;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;

import java.sql.BatchUpdateException;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletContext;

import redora.api.*;
import redora.api.fetch.*;
import redora.exceptions.*;
import redora.impl.*;
import redora.util.ResourceFileHandler;
import redora.configuration.rdo.model.RedoraConfiguration;
import redora.configuration.rdo.model.base.RedoraConfigurationBase.Status;
import redora.configuration.rdo.service.RedoraConfigurationService;
import redora.configuration.rdo.service.base.RedoraConfigurationTableUpdate;
import redora.configuration.rdo.service.base.RedoraTrashTableUpdate;
import ${objects.package}.service.base.*;

public class Upgrade extends ServiceBase {
	private Logger l = Logger.getLogger("${objects.package}.model.service.Upgrade");
	
	public final static String[] REDORA_TABLES = {"RedoraConfiguration","RedoraTrash"};

	public final static String[] OBJECTS = {
<#list doc["/objects/object/name"] as modelname>
		"${modelname}"<#if modelname_has_next>,</#if>
</#list>
	};
	
	public final static String[] RELATION_TABLES = {
<#list doc["/objects/object/attributes/set[@multiplicity='n-to-m']"] as att>
	<#assign object = att?parent?parent>
		"${att.@relationTableName}"<#if att_has_next>,</#if>
</#list>
	};
	
	public final static String[] UPGRADE_FILES = {
<#list doc["/objects/upgrades/upgrade"] as upgrade>
		"${upgrade}"<#if upgrade_has_next>,</#if>
</#list>
	};

	public Upgrade() throws ConnectException {
		super();
	}
	
	public void upgradeTable(PrintWriter out, ServletContext context)
        throws PersistenceException, IOException, SQLException {
		l.log(Level.INFO, "Found " + OBJECTS.length + " model files to process");
		if(out != null)
			out.println("Found " + OBJECTS.length + " model files to process.<br>");
<#list doc["/objects/object/name"] as modelname>
		${modelname?cap_first}TableUpdate ${modelname?lower_case}Update = new ${modelname?cap_first}TableUpdate();
		${modelname?lower_case}Update.create();
		${modelname?lower_case}Update.close();
</#list>
		
		l.log(Level.INFO, "Create RedoraConfiguration Table");
		if (out != null) {
			out.println("Create RedoraConfiguration Table.<br>");
        }

		RedoraConfigurationTableUpdate redoraConfigurationUpdate = new RedoraConfigurationTableUpdate();
		redoraConfigurationUpdate.create();
		redoraConfigurationUpdate.close();
		
		l.log(Level.INFO, "Create RedoraTrash Table");
		if (out != null) {
			out.println("Create RedoraTrash Table.<br>");
        }

		RedoraTrashTableUpdate redoraTrashTableUpdate = new RedoraTrashTableUpdate();
		redoraTrashTableUpdate.create();
		redoraTrashTableUpdate.close();
		
		l.log(Level.INFO, "Finished running create table scripts");
		if (out != null) {
			out.println("Finished running create table scripts.<br>");
        }

		executeUpgradeScript(context);
		
		connect();
		boolean retVal = false;
		try {
			rs = con.getMetaData().getTables(null, null, null, null);
			while (rs.next()) {
				retVal = false;
				for (String tableName : OBJECTS) {
					if(rs.getString("TABLE_NAME").equalsIgnoreCase(tableName)){
						retVal = true;
					}
				}
				for (String tableName : RELATION_TABLES) {
					if(rs.getString("TABLE_NAME").equalsIgnoreCase(tableName)){
						retVal = true;
					}
				}
				for (String tableName : REDORA_TABLES) {
					if(rs.getString("TABLE_NAME").equalsIgnoreCase(tableName)){
						retVal = true;
					}
				}
				if (!retVal) {
					l.log(Level.WARNING, "table " + rs.getString("TABLE_NAME") + " is not existed in xml file but it is in database");
					if (out != null) {
						out.println("Warning: table " + rs.getString("TABLE_NAME") + " is not existed in xml file but it is in database." + "<br>");
                    }
				}
			}
		} catch (SQLException e) {
			l.log(Level.SEVERE, "Failed to open meta data", e);
			throw new QueryException("Failed to open meta data", e);
		}
		
	}

    /** Drops and re-creates all tables, including the Redora* tables.*/
	public void dropAllTables() throws PersistenceException {
		l.log(Level.INFO, "DROP " + OBJECTS.length + " Object Tables");
		<#list doc["/objects/object/name"] as modelname>
		l.log(Level.INFO, "DROP ${modelname} Table");
		${modelname?cap_first}TableUpdate ${modelname?lower_case}Update = new ${modelname?cap_first}TableUpdate();
		${modelname?lower_case}Update.drop();
		${modelname?lower_case}Update.create();
		${modelname?lower_case}Update.close();
		</#list>
		l.log(Level.INFO, "DROP RedoraConfiguration Table");
		RedoraConfigurationTableUpdate redoraConfigurationUpdate = new RedoraConfigurationTableUpdate();
		redoraConfigurationUpdate.drop();
		redoraConfigurationUpdate.create();
		redoraConfigurationUpdate.close();
		l.log(Level.INFO, "DROP RedoraTrash Table");
		RedoraTrashTableUpdate redoraTrashUpdate = new RedoraTrashTableUpdate();
		redoraTrashUpdate.drop();
		redoraTrashUpdate.create();
		redoraTrashUpdate.close();

        initUpgradeScript();
	}

    /** Drops and re-creates given table.*/
	public void dropTable(String tableName) throws ConnectException, PersistException, QueryException {
		<#list doc["/objects/object/name"] as modelname>
		if (tableName.equalsIgnoreCase("${modelname}")) {
			l.log(Level.INFO, "DROP ${modelname} Table");
			${modelname?cap_first}TableUpdate ${modelname?lower_case}Update = new ${modelname?cap_first}TableUpdate();
			${modelname?lower_case}Update.drop();
            ${modelname?lower_case}Update.create();
			${modelname?lower_case}Update.close();
		}
		</#list>
	}
	
	public void check(PrintWriter out) throws ConnectException, QueryException {
		<#list doc["/objects/object/name"] as modelname>
		${modelname?cap_first}TableUpdate ${modelname?lower_case}Update = new ${modelname?cap_first}TableUpdate();
		${modelname?lower_case}Update.check(out);
		${modelname?lower_case}Update.close(); 
		</#list>
		<#list doc["/objects/object/attributes/set[@multiplicity='n-to-m']"] as att>
			<#assign object = att?parent?parent>
		checkRelationTable("${att.@relationTableName}","${att.@theirKey}","${att.@myKey}",out);
		</#list>
	}
	
	public void checkRelationTable(String tableName,String objectA,String objectB,PrintWriter out) throws ConnectException, QueryException {
		try {
			connect();
			rs = con.getMetaData().getColumns("", "", tableName, "");
			Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>();
			while (rs.next()) {
				Map<String, Object> list = new HashMap<String, Object>();
				list.put("COLUMN_NAME", rs.getString("COLUMN_NAME"));
				list.put("DATA_TYPE", rs.getInt("DATA_TYPE"));
				map.put(rs.getString("COLUMN_NAME"), list);
			}
			for (String key : map.keySet()) {
				String COLUMN_NAME = map.get(key).get("COLUMN_NAME").toString();
				if(!(COLUMN_NAME.equalsIgnoreCase("id") || COLUMN_NAME.equalsIgnoreCase(objectA) || COLUMN_NAME.equalsIgnoreCase(objectB)
					|| COLUMN_NAME.equalsIgnoreCase("creationDate") || COLUMN_NAME.equalsIgnoreCase("updateDate") || COLUMN_NAME.equalsIgnoreCase("roDeleted"))) {
					l.log(Level.WARNING, tableName + " does not have " + key
							+ " attribute but it exists in database");
					if (out != null)
						out.println(tableName + " does not have " + key
								+ " attribute but it exists in database." + "<br>");
				}
			}
			if (!map.containsKey("id")) {
				l.log(Level.WARNING,tableName + ".id does not existed or its sqltype does not match according to xml");
				if(out != null)
					out.println(tableName + ".id does not existed or its sqltype does not match according to xml." + "<br>");
			}
			if (!map.containsKey(objectA)) {
				l.log(Level.WARNING,tableName + "." + objectA + " does not existed or its sqltype does not match according to xml");
				if(out != null)
					out.println(tableName + "." + objectA + " does not existed or its sqltype does not match according to xml." + "<br>");		
			}
			if (!map.containsKey(objectB)) {
				l.log(Level.WARNING,tableName + "." + objectB + " does not existed or its sqltype does not match according to xml");
				if(out != null)
					out.println(tableName + "." + objectB + " does not existed or its sqltype does not match according to xml." + "<br>");			
			}
			if (!map.containsKey("creationDate")) {
				l.log(Level.WARNING,tableName + ".creationDate does not existed or its sqltype does not match according to xml");
				if(out != null)
					out.println(tableName + ".creationDate does not existed or its sqltype does not match according to xml." + "<br>");				
			}
			if (!map.containsKey("updateDate")) {
				l.log(Level.WARNING,tableName + ".updateDate does not existed or its sqltype does not match according to xml");
				if(out != null)
					out.println(tableName + ".updateDate does not existed or its sqltype does not match according to xml." + "<br>");
			}
		} catch (SQLException e) {
			l.log(Level.SEVERE, "Failed to execute checkRelationTable " + tableName, e);
			throw new QueryException("Failed to execute checkRelationTable " + tableName, e);
		}
	}
	
	public void executeUpgradeScript(ServletContext context)
            throws PersistenceException, IOException, SQLException {
		
		initUpgradeScript();
		
		RedoraConfigurationService service = redora.configuration.rdo.service.ServiceFactory.redoraConfigurationService();		
        
        for (RedoraConfiguration conf : service.findByStatus(Status.New, Page.ALL_TABLE)) {
			connect();
			s = con.createStatement();
			for (String sql : loadSql(conf.getScriptName(), context)) {
				s.addBatch(sql);
				l.log(Level.INFO, "Adding SQL statement: " + sql);
			}
			
			conf.setStatus(Status.Ready);
					
			try {
				l.log(Level.FINE, "Executing " + conf.getScriptName()); 
				s.executeBatch();
			} catch (BatchUpdateException e) {
				l.log(Level.SEVERE, "Failed to execute a statement in " + conf.getScriptName(), e);
				conf.setStatus(Status.Error);
				conf.setOutput(e.toString());
			}
			
			Set<BusinessRuleViolation> brViolations = service.persist(conf);
			if (!brViolations.isEmpty()) {
				String s = "One or more business rules have been violated: ";
				for (BusinessRuleViolation br : brViolations) {
					s += br.getPersistable().getClass().getName() + " id: " + br.getPersistable().getId() + " for rule: " + br.getBusinessRuleId();
				}
				throw new BusinessRuleViolationException(s);
			}        }
        
		service.close();
	}
	
	/**
	* Scans the SCRIPT_SOURCE_DIR for new upgrade scripts. Adds them to RedoraConfiguration.
	*/
	public void initUpgradeScript() throws PersistenceException {
		RedoraConfigurationService service = redora.configuration.rdo.service.ServiceFactory.redoraConfigurationService();
        for (String upgrade : UPGRADE_FILES) {
            if (service.findByScriptName(upgrade, Page.ALL_TABLE).isEmpty()) {
                RedoraConfiguration conf = new RedoraConfiguration();
                conf.setScriptName(upgrade);
                Set<BusinessRuleViolation> brViolations = service.persist(conf);
                if (!brViolations.isEmpty()) {
                    String s = "One or more business rules have been violated: ";
                    for (BusinessRuleViolation br : brViolations) {
                        s += br.getPersistable().getClass().getName() + " id: " + br.getPersistable().getId() + " for rule: " + br.getBusinessRuleId();
                    }
                    throw new BusinessRuleViolationException(s);
                }
                l.log(Level.INFO, "Found new script file " + conf.getScriptName());
            }
        }
        service.close();
	}
	
	/**
	* Locates the script file and makes a list of sql statements in the file.
	* Each SQL statement is supposed to end with ;.
	*
	* @return Empty or filled list with SQL statements.
	*/
	private List<String> loadSql(String sqlFile, ServletContext context) throws IOException {
		List<String> retval = new ArrayList<String>();
		
		InputStream in = ResourceFileHandler.findUpgradeFile(sqlFile, context);
		BufferedReader br = new BufferedReader(new InputStreamReader(in, "utf-8"));
		StringBuffer sqlSb = new StringBuffer();
		//byte[] buff = new byte[1024];
		int byteRead = 0;
		while ((byteRead = br.read()) != -1) {
            sqlSb.append((char) byteRead);
        }
        in.close();

		String[] sqlStatements = sqlSb.toString().split("(;\\s*\\r\\n)|(;\\s*\\n)");
		for (String sql : sqlStatements) {
			sql = sql.replaceAll("--.*", "").trim();
			if (!sql.equals("")) {
				retval.add(sql);
			}
		}
		return retval;
	}
	
	public static void main(String args[]) throws PersistenceException, IOException, SQLException {
		Upgrade upgrade = new Upgrade();
		upgrade.upgradeTable(null, null);
		upgrade.check(null);
		upgrade.close();
	}
}
